﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections.ObjectModel;
using PM.Entity;
using PM.Common;
using System.Data.OracleClient;
using System.Data;

namespace PM.Services
{
    public class TransactionServices
    {
        private static Transaction GetTransactionFromDataReader(IDataReader dataReader)
        {
            Transaction retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                int intTemp = 0;
                Int64 int64Temp = 0;
                double dblTemp = 0;
                retVal = new Transaction();

                intTemp = 0;
                if (int.TryParse(dataReader["TRANSACTION_ID"].ToString(), out intTemp))
                    retVal.TransactionId = intTemp;

                retVal.TradeDate = dataReader["TRADE_DATE"].ToString();
                retVal.SecurityId = dataReader["SECURITY_ID"].ToString();
                retVal.Description = dataReader["DESCRIPTION"].ToString();

                dblTemp = 0;
                if (double.TryParse(dataReader["BID_PRICE"].ToString(), out dblTemp))
                    retVal.BidPrice = dblTemp;
                dblTemp = 0;
                if (double.TryParse(dataReader["ASK_PRICE"].ToString(), out dblTemp))
                    retVal.AskPrice = dblTemp;
                dblTemp = 0;
                if (double.TryParse(dataReader["OPEN_PRICE"].ToString(), out dblTemp))
                    retVal.OpenPrice = dblTemp;
                dblTemp = 0;
                if (double.TryParse(dataReader["HIGH_PRICE"].ToString(), out dblTemp))
                    retVal.HighPrice = dblTemp;
                dblTemp = 0;
                if (double.TryParse(dataReader["LOW_PRICE"].ToString(), out dblTemp))
                    retVal.LowPrice = dblTemp;
                dblTemp = 0;
                if (double.TryParse(dataReader["LAST_TRADED_PRICE"].ToString(), out dblTemp))
                    retVal.LastTradedPrice = dblTemp;
                dblTemp = 0;
                if (double.TryParse(dataReader["CLOSE_PRICE"].ToString(), out dblTemp))
                    retVal.ClosePrice = dblTemp;

                int64Temp = 0;
                if (Int64.TryParse(dataReader["TRADE_VOLUME"].ToString(), out int64Temp))
                    retVal.TradeVolume = int64Temp;
                int64Temp = 0;
                if (Int64.TryParse(dataReader["SHARE_VOLUME"].ToString(), out int64Temp))
                    retVal.ShareVolume = int64Temp;
                dblTemp = 0;
                if (double.TryParse(dataReader["TURNOVER"].ToString(), out dblTemp))
                    retVal.TurnOver = dblTemp;
                int64Temp = 0;
                if (Int64.TryParse(dataReader["ISSUED_QUANTITY"].ToString(), out int64Temp))
                    retVal.IssuedQuantity = int64Temp;
                
            }
            return retVal;
        }

        public static Transaction GetTransactionById(int transactionId)
        {
            Transaction retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM TRANS_HISTORY WHERE TRANSACTION_ID = " + transactionId;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetTransactionFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static TransactionCollection GetTransactionCollection()
        {
            TransactionCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM TRANS_HISTORY";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new TransactionCollection();
                    while (dataReader.Read())
                    {
                        Transaction item = GetTransactionFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }


        public static DataTable GetTransactionCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM TRANS_HISTORY";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static int GetNextTransId()
        {
            int retVal = 1;
            
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();                
                OracleCommand cmd = new OracleCommand();

                cmd.CommandText = "SELECT MAX(TRANSACTION_ID) FROM TRANS_HISTORY";
                cmd.Connection = con;
                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null && !dataReader.IsClosed && dataReader.Read())
                    {
                        int intTemp = 0;
                        if (int.TryParse(dataReader["MAX(TRANSACTION_ID)"].ToString(), out intTemp))
                            retVal = intTemp + 1;
                    }
                    else
                    {
                        retVal = 1;
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static int GetNextTransId(OracleConnection con, OracleTransaction tran)
        {
            int retVal = 1;

            //OracleConnection con = new OracleConnection();

            try
            {
                //con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                OracleCommand cmd = new OracleCommand();

                cmd.CommandText = "SELECT MAX(TRANSACTION_ID) FROM TRANS_HISTORY";
                cmd.Connection = con;
                cmd.Transaction = tran;
                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null && !dataReader.IsClosed && dataReader.Read())
                    {
                        int intTemp = 0;
                        if (int.TryParse(dataReader["MAX(TRANSACTION_ID)"].ToString(), out intTemp))
                            retVal = intTemp + 1;
                    }
                    else
                    {
                        retVal = 1;
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw exc;
            }
            finally
            {
                //con.Close();
            }
            return retVal;

        }


        public static void Add(Transaction transaction)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "INSERT INTO TRANS_HISTORY" +
                                    "(  TRANSACTION_ID, TRADE_DATE, SECURITY_ID, DESCRIPTION, BID_PRICE, ASK_PRICE, OPEN_PRICE, HIGH_PRICE, LOW_PRICE, LAST_TRADED_PRICE, CLOSE_PRICE, TRADE_VOLUME, SHARE_VOLUME, TURNOVER, ISSUED_QUANTITY )" +
                                    "VALUES(    :v1,    :v2,        :v3,            :v4,        :v5,        :v6,        :v7,        :v8,      :v9,          :v10,           :v11,       :v12,           :v13,       : v14,      :v15)";

                cmd.Parameters.Add(":v1", OracleType.Number).Value = transaction.TransactionId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = transaction.TradeDate;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = transaction.SecurityId;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = transaction.Description;
                cmd.Parameters.Add(":v5", OracleType.Number).Value = transaction.BidPrice;
                cmd.Parameters.Add(":v6", OracleType.Number).Value = transaction.AskPrice;
                cmd.Parameters.Add(":v7", OracleType.Number).Value = transaction.OpenPrice;
                cmd.Parameters.Add(":v8", OracleType.Number).Value = transaction.HighPrice;
                cmd.Parameters.Add(":v9", OracleType.Number).Value = transaction.LowPrice;
                cmd.Parameters.Add(":v10", OracleType.Number).Value = transaction.LastTradedPrice;
                cmd.Parameters.Add(":v11", OracleType.Number).Value = transaction.ClosePrice;
                cmd.Parameters.Add(":v12", OracleType.Number).Value = transaction.TradeVolume;
                cmd.Parameters.Add(":v13", OracleType.Number).Value = transaction.ShareVolume;
                cmd.Parameters.Add(":v14", OracleType.Number).Value = transaction.TurnOver;
                cmd.Parameters.Add(":v15", OracleType.Number).Value = transaction.IssuedQuantity;


                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Add(Transaction transaction, OracleConnection con, OracleTransaction tran)
        {
            //OracleConnection con = new OracleConnection();
            try
            {
                //con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "INSERT INTO TRANS_HISTORY" +
                                    "(  TRANSACTION_ID, TRADE_DATE, SECURITY_ID, DESCRIPTION, BID_PRICE, ASK_PRICE, OPEN_PRICE, HIGH_PRICE, LOW_PRICE, LAST_TRADED_PRICE, CLOSE_PRICE, TRADE_VOLUME, SHARE_VOLUME, TURNOVER, ISSUED_QUANTITY )" +
                                    "VALUES(    :v1,    :v2,        :v3,            :v4,        :v5,        :v6,        :v7,        :v8,      :v9,          :v10,           :v11,       :v12,           :v13,       : v14,      :v15)";

                cmd.Parameters.Add(":v1", OracleType.Number).Value = transaction.TransactionId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = transaction.TradeDate;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = transaction.SecurityId;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = transaction.Description;
                cmd.Parameters.Add(":v5", OracleType.Number).Value = transaction.BidPrice;
                cmd.Parameters.Add(":v6", OracleType.Number).Value = transaction.AskPrice;
                cmd.Parameters.Add(":v7", OracleType.Number).Value = transaction.OpenPrice;
                cmd.Parameters.Add(":v8", OracleType.Number).Value = transaction.HighPrice;
                cmd.Parameters.Add(":v9", OracleType.Number).Value = transaction.LowPrice;
                cmd.Parameters.Add(":v10", OracleType.Number).Value = transaction.LastTradedPrice;
                cmd.Parameters.Add(":v11", OracleType.Number).Value = transaction.ClosePrice;
                cmd.Parameters.Add(":v12", OracleType.Number).Value = transaction.TradeVolume;
                cmd.Parameters.Add(":v13", OracleType.Number).Value = transaction.ShareVolume;
                cmd.Parameters.Add(":v14", OracleType.Number).Value = transaction.TurnOver;
                cmd.Parameters.Add(":v15", OracleType.Number).Value = transaction.IssuedQuantity;


                cmd.Connection = con;
                cmd.Transaction = tran;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw exc;
            }
            finally
            {
                //con.Close();
            }
        }


        public static void Delete(string transactionId)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "DELETE FROM TRANS_HISTORY WHERE TRANSACTION_ID = " + transactionId;
                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(Transaction transaction)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "UPDATE TRANS_HISTORY SET " +
                                  "TRADE_DATE = :v2, " +
                                  "SECURITY_ID = :v3, " +
                                  "DESCRIPTION = :v4, " +
                                  "BID_PRICE = :v5, " +
                                  "ASK_PRICE = :v6, " +
                                  "OPEN_PRICE = :v7, " +
                                  "HIGH_PRICE = :v8, " +
                                  "LOW_PRICE = :v9, " +
                                  "LAST_TRADED_PRICE = :v10, " +
                                  "CLOSE_PRICE = :v11, " +
                                  "TRADE_VOLUME = :v12, " +
                                  "SHARE_VOLUME = :v13, " +
                                  "TURNOVER = :v14, " +
                                  "ISSUED_QUANTITY = :v15" +                                 
                                  "WHERE TRANSACTION_ID = :v1";


                cmd.Parameters.Add(":v1", OracleType.Number).Value = transaction.TransactionId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = transaction.TradeDate;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = transaction.SecurityId;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = transaction.Description;
                cmd.Parameters.Add(":v5", OracleType.Number).Value = transaction.BidPrice;
                cmd.Parameters.Add(":v6", OracleType.Number).Value = transaction.AskPrice;
                cmd.Parameters.Add(":v7", OracleType.Number).Value = transaction.OpenPrice;
                cmd.Parameters.Add(":v8", OracleType.Number).Value = transaction.HighPrice;
                cmd.Parameters.Add(":v9", OracleType.Number).Value = transaction.LowPrice;
                cmd.Parameters.Add(":v10", OracleType.Number).Value = transaction.LastTradedPrice;
                cmd.Parameters.Add(":v11", OracleType.Number).Value = transaction.ClosePrice;
                cmd.Parameters.Add(":v12", OracleType.Number).Value = transaction.TradeVolume;
                cmd.Parameters.Add(":v13", OracleType.Number).Value = transaction.ShareVolume;
                cmd.Parameters.Add(":v14", OracleType.Number).Value = transaction.TurnOver;
                cmd.Parameters.Add(":v15", OracleType.Number).Value = transaction.IssuedQuantity;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }       

    }
}
